insert overwrite table jms_dwd.dwd_tab_door_pick_up_base_dt partition(dt)
select
    id         -- '批次号'
    ,batch_id  -- '主键id'
    ,input_time  -- '统计日期'
    ,organ_code  -- '组织编码'
    ,organ_name  -- '组织名称'
    ,province  -- '省'
    ,city  -- '市'
    ,branch_code  -- '分公司编码'
    ,cast(order_num as bigint) as order_num  -- '总单量'
    ,cast(recept_num as bigint) as recept_num -- '接单量'
    ,cast(pick_num as bigint) as pick_num -- '取件量'
    ,cast(cancel_num as bigint) as cancel_num -- '取消量'
    ,cast(overtime_pick_num as bigint) as overtime_pick_num -- '超时取件量'
    ,cast(timely_pick_num as bigint) as timely_pick_num  -- '取件及时量'
    ,cast(break_num as bigint) as break_num -- '爽约量'
    ,cast(courier_change_num as bigint) as courier_change_num -- '快递员改约量'
    ,cast(change_other_num as bigint) as change_other_num -- '改约到其他日期量'
    ,cast(REGEXP_REPLACE(timely_pick_per, '\\%', '') as decimal(16,2)) as timely_pick_per  -- '取件及时率'
    ,cast(REGEXP_REPLACE(cancel_per, '\\%', '') as decimal(16,2)) as cancel_per  -- '取消率'
    ,cast(REGEXP_REPLACE(break_per, '\\%', '') as decimal(16,2)) as break_per  -- '爽约率'
    ,cast(REGEXP_REPLACE(courier_change_per, '\\%', '') as decimal(16,2)) as courier_change_per  -- '快递员改约率'
    ,cast(complaint_num as bigint) as complaint_num  -- '投诉量'
    ,cast(complaint_again_num as bigint) as complaint_again_num  -- '二次投诉量'
    ,cast(REGEXP_REPLACE(complaint_per, '\\%', '') as decimal(16,2)) as complaint_per  -- '投诉率'
    ,cast(send_back_num as bigint) send_back_num -- '自行寄回单量'
    ,delete_flag
    ,create_time
    ,update_time
    ,cast(REGEXP_REPLACE(phone_conn_per, '\\%', '') as decimal(16,2)) phone_conn_per -- '电联率',
     ,cast(REGEXP_REPLACE(pun_phone_conn_per, '\\%', '') as decimal(16,2)) pun_phone_conn_per -- '准时电联率',
     ,cast(should_collect_num as bigint) as should_collect_num -- '应揽收量',
     ,cast(REGEXP_REPLACE(timely_collect_num, '\\%', '') as decimal(16,2))  timely_collect_num -- '揽收及时率',
     ,cast(REGEXP_REPLACE(timely_collect_per, '\\%', '') as decimal(16,2))  timely_collect_per -- '揽收及时率',
     ,cast(sign_num as bigint) sign_num -- '签收量',
     ,cast(REGEXP_REPLACE(sign_per, '\\%', '') as decimal(16,2))  sign_per -- '签收率',
     ,cast(pricing_num as bigint) pricing_num -- '核价量' ,
     ,cast(timely_pricing_num as bigint) timely_pricing_num -- '核价及时量',
     ,cast(REGEXP_REPLACE(timely_pricing_per, '\\%', '') as decimal(16,2))  timely_pricing_per -- '核价及时率',
     ,cast (wd_complaint as decimal(16,2)) wd_complaint  -- '万单投诉'
     ,data_type as data_type --数据类型 1为网点2为代理区,20231018新增
     ,date_format(input_time,'yyyy-MM-dd') as dt
from jms_ods.tab_door_pick_up
where dt = '{{ execution_date | cst_ds }}'
and date_format(input_time,'yyyy-MM-dd') = '{{ execution_date | cst_ds }}'
and delete_flag = 0;
